﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Common;
using System.Data;
using MaxLeaf.Common;
using System.Text;

namespace MaxLeaf.DB
{
    public class WebKeyMapDict
    {
        public string TableName;
        public string WebMapKeyFieldName;
        public string K3MapKeyFieldName;
        public string WebKeyValue;
        public string K3KeyValue;
    }

    public class DBAccess:IDisposable
    {
        public List<WebKeyMapDict> WebKeyMap=new List<WebKeyMapDict>();

        private ServerInfo _server = null;
        private DbConnection _conn = null;
        private DbTransaction  _Trans = null;
         
        private DBAccess()
        {
        }
        public DBAccess(ServerInfo server)
        {
            _server = server;
        }

        public DbConnection Connection
        {
            get
            {
                if (_conn == null)
                {
                    _conn = CreateDbConnection(_server.Type, CreateConnectionString(_server));
                }

                return _conn;
            }
        }
         
         

        private   DbConnection CreateDbConnection(ServerType serverType, string strConn)
        {
            DbConnection retConnection = null;
            if (serverType== ServerType.K3 )
            {
                retConnection= K3DBFactory.CreateDbConnection( strConn);
            }
            else
            {
                retConnection= WebDBFactory.CreateDbConnection( strConn);
            }
            
           
            return retConnection;
        }

        private DbDataAdapter CreateDataAdapter(ServerType serverType,string strSql)
        {
            if (serverType== ServerType.K3 )
            {
                return K3DBFactory.CreateDataAdapter(strSql, this.Connection);
            }
            else
            {
                return WebDBFactory.CreateDataAdapter(strSql, this.Connection);
            }
        }

        private DbCommand CreateCommand(ServerType serverType)
        {
            if (serverType== ServerType.K3 )
            {
                return K3DBFactory.CreateCommand(this.Connection);
            }
            else
            {
                return WebDBFactory.CreateCommand( this.Connection);
            }
        }

        private string CreateConnectionString(ServerInfo server)
        {
            if (server.Type== ServerType.K3)
            {
                return K3DBFactory.CreateConnectionString(server);
            }
            else
            {
                return WebDBFactory.CreateConnectionString(server);
            }
        }

        public  KeyValuePair<string, IDataParameter[]> CreateParamSql( string strFieldName, string strTableName, DataRow dr, LogStatus logStatus, string strKeyFieldName)
        {
            if (_server.Type == ServerType.K3)
            {
                return K3DBFactory.CreateParamSql(  strFieldName,   strTableName,   dr,   logStatus,   strKeyFieldName);
            }
            else
            {
                return WebDBFactory.CreateParamSql(strFieldName, strTableName, dr, logStatus, strKeyFieldName);
            }
        }

        public  DataTable GetDataTable(   string strSql)
        {
            return GetDataTable(strSql,null);
        }
        public  DataTable GetDataTable(   string strSql, IDataParameter[] arrParam)
        {
            LogManager.Debug(string.Format("[{0}] {1}", _server.ToString(), strSql));
            if (arrParam != null)
            {
                LogManager.Debug(GetParamLog(arrParam));
            }


            using (DbDataAdapter myadp = CreateDataAdapter(_server.Type, strSql))
            {

                using (DbCommand myCmd = myadp.SelectCommand)
                {
                    myCmd.CommandTimeout = 60;
                    if (_blnInTrans)
                    {
                        myCmd.Transaction = _Trans;
                    }
                    if (myCmd.Parameters.Count > 0)
                    {
                        myCmd.Parameters.Clear();
                    }
                    if (arrParam != null)
                    {
                        myCmd.Parameters.AddRange(arrParam);
                    }

                    DataSet myds = new DataSet();

                    myadp.Fill(myds);



                    return myds.Tables[0];
                }
            }
             
        }
        public  DataRow GetDataFirstRow(   string strSql)
        {
            return GetDataFirstRow( strSql,null);
        }
        public  DataRow GetDataFirstRow(   string strSql, IDataParameter[] arrParam)
        {
            DataTable dt = GetDataTable( strSql, arrParam);
            if (dt.Rows.Count == 0)
            {
                return null;
            }
            else
            {
                return dt.Rows[0];
            }
        }

        public  string GetValue(   string strSql)
        {
            return GetValue( strSql, null);
        }
        public string GetValue(string strSql, IDataParameter[] arrParam)
        {
            return GetValue(strSql, arrParam, string.Empty);
        }
        public  string GetValue(   string strSql, IDataParameter[] arrParam,string strOutputParaName)
        {
            return Execute(strSql, arrParam, true, strOutputParaName);


        }

        public  void ExecuteSql(    string strSql)
        {
            ExecuteSql(   strSql, null);
        }
        public  void ExecuteSql(    string strSql,IDataParameter[] arrParam)
        {
            Execute( strSql, arrParam,false);

            
        }
        private string Execute(string strSql, IDataParameter[] arrParam, bool blnNeedRt)
        {
            return Execute(strSql, arrParam, blnNeedRt, string.Empty);
        }
        private string Execute(string strSql, IDataParameter[] arrParam, bool blnNeedRt, string strOutputParaName)
        {
            LogManager.Debug(string.Format("[{0}] {1}", _server.ToString(), strSql));
            if (arrParam != null)
            {
                LogManager.Debug(GetParamLog(arrParam));
            }

            if (Connection.State == ConnectionState.Closed)
            {

                Connection.Open();
            }

            using (DbCommand myCmd = CreateCommand(_server.Type))
            {
                myCmd.CommandTimeout = 60;
                if (_blnInTrans)
                {
                    myCmd.Transaction = _Trans;
                }
                myCmd.CommandText = strSql;
                if (myCmd.Parameters.Count > 0)
                {
                    myCmd.Parameters.Clear();
                }
                int iOutputPara = -1;
                if (arrParam != null)
                {
                    if (strOutputParaName.Length > 0)
                    {
                        for (int i = 0; i < arrParam.Length; i++)
                        {
                            if (arrParam[i].ParameterName.Equals((this.ServerType == ServerType.K3 ? "@" : "?") + strOutputParaName, StringComparison.InvariantCultureIgnoreCase))
                            {
                                arrParam[i].Direction = ParameterDirection.Output;
                                iOutputPara = i;
                            }
                        }
                    }
                    myCmd.Parameters.AddRange(arrParam);
                }


                if (blnNeedRt)
                {
                    if (strOutputParaName.Length > 0 && iOutputPara >= 0)
                    {

                        myCmd.ExecuteNonQuery();
                        return arrParam[iOutputPara].Value.ToString();
                    }
                    else
                    {
                        return CvtFunc.ToString(myCmd.ExecuteScalar());
                    }
                }
                else
                {
                    return myCmd.ExecuteNonQuery().ToString();
                }
            }

        }

        private  string GetParamLog(IDataParameter[] arrParam)
        {
            StringBuilder str = new StringBuilder();
            foreach (IDataParameter param in arrParam)
            {
                str.AppendFormat("{0},",param.Value);
            }

            return str.ToString();
        }

        public  DataRow  GetTableDataFirstRow(   string strTable,Dictionary<string , object > dictCond )
        {
            return GetTableDataFirstRow( strTable, dictCond, "*");
        }
        public  DataRow GetTableDataFirstRow(   string strTable, Dictionary<string, object> dictCond, string strFieldName)
        {
            DataTable dt = GetTableData( strTable, dictCond, strFieldName);
            if (dt.Rows.Count == 0)
            {
                return null;
            }
            else
            {
                return dt.Rows[0];
            }
        }

        public  DataTable  GetTableData(   string strTable,Dictionary<string , object > dictCond )
        {
            return GetTableData( strTable, dictCond,"*");
        }
        public  DataTable GetTableData(   string strTable, Dictionary<string, object> dictCond,string strFieldName)
        {
            

            KeyValuePair<string, IDataParameter[]> kvp = CreateCondParam(dictCond);

            string strSql =string.Format( "select {0} from {1}  where {2}",strFieldName,strTable,kvp.Key);
            return GetDataTable( strSql, kvp.Value);
        }

        public   KeyValuePair<string, IDataParameter[]> CreateCondParam( Dictionary<string, object> dictCond)
        {
            if (_server.Type == ServerType.K3)
            {
                return K3DBFactory.CreateCondParam(dictCond);
            }
            else
            {
                return WebDBFactory.CreateCondParam(dictCond);
            }
        }

        public void Dispose()
        {
            if (_Trans != null)
            {
                if (_blnInTrans)
                {
                    _blnInTrans = false;
                    _Trans.Rollback();
                }
                _Trans.Dispose();
                _Trans = null;
            }
            if (_conn != null)
            {
                _conn.Close();
               
            }
        }



        public ServerType ServerType { get { return _server.Type; } }
        public bool ServerMasterDB { get { return _server.MasterDB; } }
        public string  ServerDBName { get { return _server.Database; } }

        public string ParamSign { get { return (_server.Type== Common.ServerType.K3 ?"@":"?"); } }

        private bool _blnInTrans = false;
        public void BeginTrans()
        {
            if (Connection.State == ConnectionState.Closed)
            {
                Connection.Open();
            }
            _Trans = Connection.BeginTransaction();
            _blnInTrans = true;
        }

        public void RollbackTrans()
        {
            if (_blnInTrans)
            {
                _blnInTrans = false;
                _Trans.Rollback();
            }
        }

        public void CommitTrans()
        {
            if (_blnInTrans)
            {
                _blnInTrans = false;

                _Trans.Commit();
            }
        }

        public List<string> GetAllServers()
        {
            List<string> lst = new List<string>();
            string strSql = "select   name   from   master..sysdatabases";
            DataTable dt = GetDataTable(strSql);
            foreach (DataRow dr in dt.Rows)
            {
                lst.Add(CvtFunc.ToString(dr["name"]));
            }

            return lst;
        }
    }
}